package cn.neusoft.dao.impl;

import cn.neusoft.bean.Business;
import cn.neusoft.bean.DeliveryAddress;
import cn.neusoft.dao.IDeliveryAddressDao;
import cn.neusoft.query.BaseQuery;
import cn.neusoft.query.BusinessQuery;
import cn.neusoft.query.DeliveryAddressQuery;
import cn.neusoft.utils.JdbcUtils;
import com.alibaba.druid.util.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDao implements IDeliveryAddressDao {

    private QueryRunner queryRunner;

    public DeliveryAddressDao() {
        queryRunner = JdbcUtils.getQueryRunner();
    }

    @Override
    public int add(DeliveryAddress deliveryAddress) throws SQLException {
        String sql ="INSERT INTO deliveryaddress(contactName, contactSex, contactTel, address, userId) VALUES(?,?,?,?,?)";
        return queryRunner.update(sql,
                deliveryAddress.getContactName(),
                deliveryAddress.getContactSex(),
                deliveryAddress.getContactTel(),
                deliveryAddress.getAddress(),
                deliveryAddress.getUserId());
    }

    @Override
    public int update(DeliveryAddress deliveryAddress) throws SQLException {
        String sql = "update deliveryaddress set contactName=?, contactSex=?, contactTel=?, address=?, userId=? where daId=?";
        return queryRunner.update(sql,
                deliveryAddress.getContactName(),
                deliveryAddress.getContactSex(),
                deliveryAddress.getContactTel(),
                deliveryAddress.getAddress(),
                deliveryAddress.getUserId(),
                deliveryAddress.getDaId());
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from deliveryaddress where daId=?";
        return queryRunner.update(sql,id);
    }

    @Override
    public DeliveryAddress getById(Integer id) throws SQLException {
        String sql = "select daId,contactName, contactSex, contactTel, address, userId from deliveryaddress where daId=?";
        return queryRunner.query(sql,new BeanHandler<DeliveryAddress>(DeliveryAddress.class),id);
    }

    @Override
    public List<DeliveryAddress> getAll() throws SQLException {
        String sql = "select daId,contactName, contactSex, contactTel, address, userId from deliveryaddress";
        return queryRunner.query(sql,new BeanListHandler<DeliveryAddress>(DeliveryAddress.class));
    }

    @Override
    public int getCount(BaseQuery baseQuery) throws SQLException {
        DeliveryAddressQuery query = (DeliveryAddressQuery)baseQuery;
        List<Object> params = new ArrayList<>();
        String sql = "select count(daId) from deliveryaddress where 1=1 ";
        if(!StringUtils.isEmpty(query.getContactName())) {
            sql += " and contactName like concat('%',?,'%')";
            params.add(query.getContactName());
        }
        System.out.println("查询总记录："+sql);
        System.out.println("参数集合："+params);
        return queryRunner.query(sql, new ScalarHandler<Long>(), params.toArray()).intValue();
    }

    @Override
    public List<DeliveryAddress> getByPage(BaseQuery baseQuery) throws SQLException {
        DeliveryAddressQuery query = (DeliveryAddressQuery)baseQuery;
        List<Object> params = new ArrayList<>();
        //可以动态拼接条件搜索的部分  where 1=1
        String sql = "select daId,contactName, contactSex, contactTel, address, userId from deliveryaddress" +
                " where 1=1";
        //动态sql  根据条件参数的传递来确定要不要拼接sql
        if(!StringUtils.isEmpty(query.getContactName())) {
            sql += " and contactName like concat('%',?,'%')";
            params.add(query.getContactName());
        }
        sql += " ORDER BY daId DESC limit ?,?";
        //给limit后面的两个?填充的  一直都有
        params.add(query.getStartIndex());
        params.add(query.getPageCount());

        System.out.println("查询集合："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql, new BeanListHandler<DeliveryAddress>(DeliveryAddress.class), params.toArray());
    }
}
